package com.guodi.map.utils;

import com.guodi.common.utils.EncodingUtil;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @描述：excel解析操作类
 * @作者：邹艺
 * @日期：2019/5/14 18:19
 **/
public class ReadExcelUtil {

    /**
     * @描述：解析Excel
     * @作者：彭辉
     * @时间：2019/8/31 10:43
     */
    public static List<List<String>> getExcelData(InputStream inputStream, boolean isE2007) {
        try {
            Workbook wb;
            //根据文件格式(2003或者2007)来初始化
            if (isE2007) {
                wb = new XSSFWorkbook(inputStream);
            } else {
                wb = new HSSFWorkbook(inputStream);
            }
            //获取第一个表单
            Sheet sheet = wb.getSheetAt(0);
            List<List<String>> dataList = getSheetData(sheet);
            return dataList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * @描述：根据Excel路径解析并获取Excel数据集
     * @作者：彭辉
     * @时间：2019/9/1 9:52
     */
    public static List<List<String>> getExcelData(String filePath, boolean isToTable) {
        List<List<String>> dataList = null;
        boolean isE2007 = false;
        // 判断是否是excel2007格式
        if (filePath.endsWith(".xlsx")) {
            isE2007 = true;
        }
        HttpURLConnection urlConnection = null;
        try {
            filePath = EncodingUtil.encode(filePath);
            URL httpUrl = new URL(filePath);
            urlConnection = (HttpURLConnection) httpUrl.openConnection();
            Workbook wb;
            // 根据文件格式(2003或者2007)来初始化
            if (isE2007) {
                wb = new XSSFWorkbook(urlConnection.getInputStream());
            } else {
                wb = new HSSFWorkbook(urlConnection.getInputStream());
            }
            // 获取第一个表单
            Sheet sheet = wb.getSheetAt(0);
            if(isToTable){
                dataList = shteetToList(sheet);
            }else{
                dataList = getSheetData(sheet);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (urlConnection != null) {
                urlConnection.disconnect();
            }
        }
        return dataList;
    }

    /**
     * @描述：根据上传MultipartFile对象获取excel数据集
     * @作者：彭辉
     * @时间：2019/9/1 9:44
     */
    public static List<List<String>> getExcelData(MultipartFile file) throws Exception {
        String fileName = file.getOriginalFilename();
        Workbook wb = null;
        InputStream is = file.getInputStream();
        // 根据文件格式(2003或者2007)来初始化
        if (fileName.endsWith(".xlsx")) {
            wb = new XSSFWorkbook(is);
        } else {
            wb = new HSSFWorkbook(is);
        }
        // 默认读取第一个表单
        Sheet sheet = wb.getSheetAt(0);
        // Sheet解析成List数据
        List<List<String>> dataList = getSheetData(sheet);
        return dataList;
    }

    /**
     * @描述：Sheet解析成List数据
     * @作者：彭辉
     * @时间：2019/9/1 9:50
     */
    public static List<List<String>> shteetToList(Sheet sheet) {
        List<List<String>> list = new ArrayList();
        int rowCount = sheet.getLastRowNum();
        int columns = getColumnCount(sheet);
        for(int i = 0; i <= rowCount; ++i) {
            // 空行不显示
            if (isBlankRow(sheet.getRow(i))){
                list.add(new ArrayList());
                continue;
            }
            List<String> temp = new ArrayList();
            int columnLength;
            if(i==0){
                columnLength = sheet.getRow(0).getPhysicalNumberOfCells();
            }else{
                columnLength = columns;
            }
            for(int j = 0; j < columnLength; ++j) {
                temp.add(cellToTd(sheet, i, j));
            }
            list.add(temp);
        }
        return list;
    }

    /**
     * @描述：判断是不是空行
     * @作者：陈宏成
     * @时间：2021/02/23 15:00
     */
    private static boolean isBlankRow(Row row){
        if(row == null){
            return true;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != CellType.BLANK){
                return false;
            }
        }
        return true;
    }

    /**
     * @描述：Cell解析成table的td
     * @作者：彭辉
     * @时间：2019/9/1 9:50
     */
    private static String cellToTd(Sheet sheet, int row, int column) {
        Cell cel = sheet.getRow(row).getCell(column);
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    //合并的第一个单元格不作判断
                    if (row == firstRow && column == firstColumn) {
                        String cellValue = getCellValue(cel);
                        return "<td rowspan='" + (lastRow - firstRow + 1) + "' colspan='" + (lastColumn - firstColumn + 1) + "'>" + cellValue + "</td>";
                    } else {
                        return "";
                    }
                }
            }
        }
        String cellValue = getCellValue(cel);
        return "<td>" + cellValue + "</td>";
    }

    /**
     * @描述：获取单元格的值
     * @作者：彭辉
     * @时间：2019/9/1 9:49
     */
    public static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        String cellValue = null;
        try {
            Date date = cell.getDateCellValue();
            //判断是日期
            if (date!=null&&date.getTime()>0){
                SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                cellValue = df.format(date);
            }else {
                cell.setCellType(CellType.STRING);
                cellValue = cell.getStringCellValue();
            }
        }catch (Exception e){
            cell.setCellType(CellType.STRING);
            cellValue = cell.getStringCellValue();
        }
        return cellValue;
    }

    /**
     * @描述：合并单元格处理,获取合并行
     * @作者：彭辉
     * @时间：2019/9/1 9:48
     */
    public static List<CellRangeAddress> getCombineCell(Sheet sheet) {
        List<CellRangeAddress> list = new ArrayList<>();
        // 获得一个 sheet 中合并单元格的数量
        int sheetmergerCount = sheet.getNumMergedRegions();
        // 遍历所有的合并单元格
        for (int i = 0; i < sheetmergerCount; i++) {
            //获得合并单元格保存进list中
            CellRangeAddress ca = sheet.getMergedRegion(i);
            list.add(ca);
        }
        return list;
    }

    private static int getRowNum(List<CellRangeAddress> listCombineCell, Cell cell) {
        int xr = 0;
        int firstC = 0;
        int lastC = 0;
        int firstR = 0;
        int lastR = 0;
        for (CellRangeAddress ca : listCombineCell) {
            // 获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
                if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
                    xr = lastR;
                }
            }

        }
        return xr;
    }

    /**
     * @描述：判断单元格是否为合并单元格，是的话则将单元格的值返回
     * @参数：listCombineCell 存放合并单元格的list, cell 需要判断的单元格
     * @作者：彭辉
     * @时间：2019/9/1 9:47
     */
    public static String isCombineCell(List<CellRangeAddress> listCombineCell, Cell cell, Sheet sheet)
            throws Exception {
        int firstC = 0;
        int lastC = 0;
        int firstR = 0;
        int lastR = 0;
        String cellValue = null;
        for (CellRangeAddress ca : listCombineCell) {
            // 获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
                if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
                    Row fRow = sheet.getRow(firstR);
                    Cell fCell = fRow.getCell(firstC);
                    cellValue = getCellValue(fCell);
                    break;
                }
            } else {
                cellValue = "";
            }
        }
        return cellValue;
    }

    /**
     * @描述：获取合并单元格的值
     * @作者：彭辉
     * @时间：2019/9/1 9:46
     */
    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }
        return null;
    }

    /**
     * @描述：判断指定的单元格是否是合并单元格
     * @作者：彭辉
     * @时间：2019/9/1 9:45
     */
    public static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * @描述：Sheet解析成List数据
     * @作者：彭辉
     * @时间：2019/9/1 9:43
     */
    private static List<List<String>> getSheetData(Sheet sheet) {
        List<List<String>> list = new ArrayList<>();
        int rowCount = sheet.getLastRowNum();

        // 格式化日期
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for (int i = 0; i < rowCount + 1; i++) {
            Row row = sheet.getRow(i);
            if(null != row) {
                List<String> temp = new ArrayList<>();
                for (int j = 0; j < sheet.getRow(0).getPhysicalNumberOfCells(); j++) {
                    Cell cellValue = row.getCell(j);
                    if (cellValue == null) {
                        temp.add("");
                    } else if (cellValue.getCellType() == CellType.NUMERIC && HSSFDateUtil.isCellDateFormatted(cellValue)) {
                        temp.add(sdf.format(cellValue.getDateCellValue()));
                    } else {
                        temp.add(cellValue.toString());
                    }
                }
                if(temp.get(0) == "" && temp.get(1) == ""){
                    break;
                }
                list.add(temp);
            }
        }
        return list;
    }

    /**
     * @描述：获取excel单元格数量(处理合并单元格),
     * @作者：邹艺
     * @时间：2019/11/10 17:27
     */
    private static int getColumnCount(Sheet sheet) {
        int nums = sheet.getRow(0).getPhysicalNumberOfCells();
        for(int j = 0; j < sheet.getRow(0).getPhysicalNumberOfCells(); j++) {
            if(isMergedRegion(sheet, 0, j)){
                int sheetMergeCount = sheet.getNumMergedRegions();
                for (int i = 0; i < sheetMergeCount; i++) {
                    CellRangeAddress range = sheet.getMergedRegion(i);
                    int firstColumn = range.getFirstColumn();
                    int lastColumn = range.getLastColumn();
                    int firstRow = range.getFirstRow();
                    int lastRow = range.getLastRow();
                    if (0 == firstRow && 0 <= lastRow) {
                        if (j >= firstColumn && j <= lastColumn) {
                            //合并的第一个单元格不作判断
                            if (0 == firstRow && j == firstColumn) {
                                nums += lastColumn - firstColumn;
                            }
                        }
                    }
                }
            }
        }
        return nums;
    }
}
